<?php
require_once ("album.php");
class AlbumDAO {
	function __construct() {
		$dsn = 'sqlite:E:\Cours\SIL3\ImageMVC\imageDB'; // Data source name
		                                                // dsn = 'sqlite:C:\wamp\www\projetSIL3PHP\imageDB'; // Data source name
		
		$user = ''; // Utilisateur
		$pass = ''; // Mot de passe
		try {
			$this->db = new PDO ( $dsn, $user, $pass, array (
					PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING 
			) ); // $db est un attribut privé d'ImageDAO
		} catch ( PDOException $e ) {
			die ( "Erreur : " . $e->getMessage () );
		}
	}
	
	/************************************************************************************************
	 * 											GESTION DE L ALBUM *
	 * ***********************************************************************************************/
	function getAlbums() {
		$s = $this->db->query ( " SELECT id,nom from Album;" );
		if ($s) {
			$result = $s->fetchAll ();
			foreach ( $result as $iter ) {
				// Sous requete pour récuperer le nombre de photos affectées
				$sub_query = $this->db->query ( "SELECT count(*) as nb from associationAlbumImage where idAlbum=" . $iter ["id"] . ";" );
				$sub_result = $sub_query->fetch ( PDO::FETCH_ASSOC );
				$returnArray [$iter ["id"]] = new Album ( $iter ["id"], $iter ["nom"], $sub_result ["nb"] );
			}
			return $returnArray;
		} else {
			print "Error in getAlbums </br>";
			$err = $this->db->errorInfo ();
			print $err [2] . "<br/>";
		}
	}
	function creerAlbum($nom) {
		$sql = "INSERT INTO `Album`(id,nom) Values (:id,:nom);";
		$statement = $this->db->prepare ( $sql );
		$id = $this->getNextIdAlbum ();
		$statement->bindParam ( ":id", $id );
		$statement->bindParam ( ":nom", $nom );
		$s = $statement->execute ();
		if ($s === FALSE) {
			// Requête mal construite
			print "Error in insert Query for creerAlbum ";
			$err = $this->db->errorInfo ();
			print $err [2] . "<br/>";
		} elseif ($s == 0) {
			
		} else {
			return new Album ( $id, $nom, 0 );
		}
	}
	function viderAlbum($idAlbum) {
		$sql = "DELETE FROM AssociationAlbumImage WHERE idAlbum = :idAlbum;";
		$statement = $this->db->prepare ( $sql );
		$statement->bindParam ( ":idAlbum", $idAlbum );
		$s = $statement->execute ();
		if ($s === FALSE) {
			// Requête mal construite
			print "Error in delete Query for viderAlbum ";
			$err = $this->db->errorInfo ();
			print $err [2] . "<br/>";
		} 
		return $s;
	}
	function supprimerAlbum($idAlbum) {
		$sql = "DELETE FROM Album WHERE id = :idAlbum;";
		$statement = $this->db->prepare ( $sql );
		$statement->bindParam ( ":idAlbum", $idAlbum );
		$s = $statement->execute ();
		if ($s === FALSE) {
			// Requête mal construite
			print "Error in delete Query for supprimerAlbum ";
			$err = $this->db->errorInfo ();
			print $err [2] . "<br/>";
		} 
		return $s;
	}
	
	/*************************************************************************************************
	 									* GESTION DES IMAGES DE L ALBUM *
	 ************************************************************************************************/
	
	function verifierAffectionImageAlbum($idAlbum, $idImage) {
		 
		$s = $this->db->query ( "SELECT count(*) as nb from associationAlbumImage where idAlbum = $idAlbum and idImage = $idImage ;" );
		if ($s) {
			$result = $s->fetch ( PDO::FETCH_ASSOC );
			return $result ["nb"];
		} else {
			print "Error in verifierAffectionImageAlbum.<br/>";
			$err = $this->db->errorInfo ();
			print $err [2] . "<br/>";
		}
	}
	
	
	function ajouterImageAlbum($idAlbum, $idImage) {
		// Par défaut, nous inserons à la derniere place
		$sql = "INSERT INTO associationAlbumImage VALUES (:idAlbum,:idImage,:ordre);";
		$statement = $this->db->prepare ( $sql );
		
		$ordre = $this->getNextOrdreImagePourAlbum ( $idAlbum );
		print "ordre: $ordre";
		$statement->bindParam ( ":idAlbum", $idAlbum );
		$statement->bindParam ( ":idImage", $idImage );
		$statement->bindParam ( ":ordre", $ordre );
		$s = $statement->execute ();
		if ($s === FALSE) {
			// Requête mal construite
			print "Error in insert Query for ajouterImageAlbum ";
			$err = $this->db->errorInfo ();
			print $err [2] . "<br/>";
		}
	}
	function supprimerImageAlbum($idAlbum, $idImage, $ordreCourant) {
		$sql = "DELETE FROM associationAlbumImage WHERE idAlbum = :idAlbum and idImage = :idImage";
		$statement = $this->db->prepare ( $sql );
		$statement->bindParam ( ":idAlbum", $idAlbum );
		$statement->bindParam ( ":idImage", $idImage );
		$s = $statement->execute ();
		if ($s === FALSE) {
			// Requête mal construite
			print "Error in delete Query for supprimerImageAlbum ";
			$err = $this->db->errorInfo ();
			print $err [2] . "<br/>";
		}
		
		// Il faut nettoyer les trous laissés dans l'ordre des photos par la suppression :
		$this->majOrdreImageAlbumSequence ( $idAlbum, $idImage, $ordreCourant, '>', - 1 );
	}
	
	/************************************************************************************************
												 * UTILITAIRES *
	 * ***********************************************************************************************/
	
	// Fonction necessaire sous l'absence de séquences dans le SGDB
	function getNextIdAlbum() {
		$s = $this->db->query ( 'SELECT max(id) as max from Album;' );
		if ($s) {
			$result = $s->fetch ( PDO::FETCH_ASSOC );
			return (( int ) $result ["max"]) + 1;
		} else {
			print "Error in getNextIdAlbum.<br/>";
			$err = $this->db->errorInfo ();
			print $err [2] . "<br/>";
		}
	}
	
	// Fonction necessaire sous l'absence de séquences dans le SGDB
	function getNextOrdreImagePourAlbum($idAlbum) {
		$s = $this->db->query ( "SELECT max(ordreImage) as max from associationAlbumImage where idAlbum = $idAlbum;" );
		if ($s) {
			$result = $s->fetch ( PDO::FETCH_ASSOC );
			return (( int ) $result ["max"]) + 1;
		} else {
			print "Error in getNextOrdreImagePourAlbum.<br/>";
			$err = $this->db->errorInfo ();
			print $err [2] . "<br/>";
		}
	}
	
	// onction permettant de mettre à jour les indices des albums à partir d'un indice
	function majAlbumSequence($id) {
		$sql = "UPDATE Album SET id = id+1 WHERE id > :id";
		$statement = $this->db->prepare ( $sql );
		$statement->bindParam ( ":id", $id );
		$s = $statement->execute ();
		if ($s === FALSE) {
			// Requête mal construite
			print "Error in update Query for majAlbumSequence ";
			$err = $this->db->errorInfo ();
			print $err [2] . "<br/>";
		} 
	}
	
	function permuterImageAlbumSequence($idAlbum, $idImage,$ordre, $sens = "up") {
		$val =1;
		if ($sens=="up") {
		 	$val*=-1;
		}
		# On change la valeur de l'image courante
		$sql = "UPDATE associationAlbumImage SET ordreImage = ordreImage+(:val) WHERE ordreImage = :ordre and idAlbum = :idAlbum";
	
		$statement = $this->db->prepare ( $sql );
		$statement->bindParam ( ":idAlbum", $idAlbum );
		$statement->bindParam ( ":ordre", $ordre );
		$statement->bindParam ( ":val", $val);
		$s = $statement->execute ();
		if ($s === FALSE) {
			// Requête mal construite
			print "Error in update Query for permuterImageAlbumSequence - image actuelle";
			$err = $this->db->errorInfo ();
			print $err [2] . "<br/>";
		}
		
		$ordre+=$val;
		$val*=-1;
	
		#Puis celle de la suivante ou précédente
		$sql = "UPDATE associationAlbumImage SET ordreImage = ordreImage+(:val) WHERE ordreImage = :ordre and idAlbum = :idAlbum and idImage != :idImage";
		
		$statement = $this->db->prepare ( $sql );
		$statement->bindParam ( ":idAlbum", $idAlbum );
		$statement->bindParam ( ":ordre", $ordre );
		$statement->bindParam ( ":val", $val);
		$statement->bindParam ( ":idImage", $idImage);
		
		$s = $statement->execute ();
		if ($s === FALSE) {
			// Requête mal construite
			print "Error in update Query for permuterImageAlbumSequence - image suivante / precente";
			$err = $this->db->errorInfo ();
			print $err [2] . "<br/>";
		}
	}
		
		
	// onction permettant de mettre à jour les indices des images par albums à partir d'un ordre
	function majOrdreImageAlbumSequence($idAlbum, $idImage, $ordre, $sens = ">", $val = -1) {
		if ($sens == "<") {
			$sql = "UPDATE associationAlbumImage SET ordreImage = ordreImage+(:val) WHERE ordreImage < :ordre and idAlbum=:idAlbum";
		} else {
			$sql = "UPDATE associationAlbumImage SET ordreImage = ordreImage+(:val) WHERE ordreImage > :ordre and idAlbum=:idAlbum";
		}
		$statement = $this->db->prepare ( $sql );
		$statement->bindParam ( ":idAlbum", $idAlbum );
		$statement->bindParam ( ":ordre", $ordre );
		$statement->bindParam (":val",$val);
		$s = $statement->execute ();
		if ($s === FALSE) {
			// Requête mal construite
			print "Error in update Query for majOrdreImageAlbumSequence ";
			$err = $this->db->errorInfo ();
			print $err [2] . "<br/>";
		}
	}
}

?>